﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace Assignment_3.Classes
{
    public class DatabaseManager
    {
        #region Booking
        /* 
         * ---------------------------------------
         * ---------- FEATURE - BOOKING ----------
         * ---------- DATABASE METHODS -----------
         * ---------------------------------------
         */
        /* 
         * ---------- INSERT -----------
         */
        // Create - GUEST
        public static int CreateGuest(Guest g)
        {
            int rowadded = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Guest(Name, Phone, Email, Address, PostalCode, Country, PaymentType)" +
                    "VALUES(@name, @phone, @email, @address, @postalCode, @country, @paymentType)";

                comm.Parameters.AddWithValue("@name", g.Name);
                comm.Parameters.AddWithValue("@phone", g.Phone);
                comm.Parameters.AddWithValue("@email", g.Email);
                comm.Parameters.AddWithValue("@address", g.Address);
                comm.Parameters.AddWithValue("@postalCode", g.PostalCode);
                comm.Parameters.AddWithValue("@country", g.Country);
                comm.Parameters.AddWithValue("@paymentType", g.PaymentType);

                rowadded = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowadded;
        }

        // Create - BOOKING
        public static int CreateBooking(Booking b)
        {
            int rowadded = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Booking(Check_In_Date, Check_Out_Date, No_Of_Rooms, No_Of_Adults, No_Of_Children, GuestID, StaffID)" +
                    "VALUES(@check_in_date, @check_out_date, @no_of_rooms, @no_of_adults, @no_of_children, @guestID, @staffID)";

                comm.Parameters.AddWithValue("@check_in_date", b.Check_In_Date);
                comm.Parameters.AddWithValue("@check_out_date", b.Check_Out_Date);
                comm.Parameters.AddWithValue("@no_of_rooms", b.No_Of_Rooms);
                comm.Parameters.AddWithValue("@no_of_adults", b.No_Of_Adults);
                comm.Parameters.AddWithValue("@no_of_children", b.No_Of_Children);
                comm.Parameters.AddWithValue("@guestID", b.GuestID);
                comm.Parameters.AddWithValue("@StaffID", b.StaffID);

                rowadded = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowadded;
        }

        // Create - ROOM
        public static int CreateRoom(Room r)
        {
            int rowadded = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Room(RoomNo, Room_Type, Bed_Type, Status, Room_Rates, BookingID)" +
                    "VALUES(@roomNo, @room_type, @bed_type, @status, @room_rates, @bookingID)";

                comm.Parameters.AddWithValue("@roomNo", r.RoomNo);
                comm.Parameters.AddWithValue("@room_type", r.Room_Type);
                comm.Parameters.AddWithValue("@bed_type", r.Bed_Type);
                comm.Parameters.AddWithValue("@status", r.Status);
                comm.Parameters.AddWithValue("@room_rates", r.Room_Rates);
                comm.Parameters.AddWithValue("@bookingID", r.BookingID);

                rowadded = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowadded;
        }

        // Create - PAYMENT
        public static int CreatePayment(Payment p)
        {
            int rowadded = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Payment(CardholderName, ExpiryDate, CreditCardNo, GuestID)" +
                    "VALUES(@cardholderName, @expiryDate, @creditCardNo, @guestID)";

                comm.Parameters.AddWithValue("@cardholderName", p.CardholderName);
                comm.Parameters.AddWithValue("@expiryDate", p.ExpiryDate);
                comm.Parameters.AddWithValue("@creditCardNo", p.CreditCardNo);
                comm.Parameters.AddWithValue("@guestID", p.GuestID);

                rowadded = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowadded;
        }

        /* 
         * ---------- RETRIEVE & VALIDATE -----------
         */
        // Retrieve GuestID
        public static int RetrieveGuestID()
        {
            int guestID = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT TOP 1 guestID FROM [HMS].[dbo].[Guest] ORDER BY guestID DESC";

                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    guestID = Convert.ToInt32(dr["guestID"]);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return guestID;
        }

        // Retrieve StaffID
        public static int RetrieveStaffID()
        {
            int staffID = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT TOP 1 staffID FROM [HMS].[dbo].[Staff] ORDER BY staffID DESC";

                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    staffID = Convert.ToInt32(dr["staffID"]);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return staffID;
        }

        // Retrieve BookingID
        public static int RetrieveBookingID()
        {
            int bookingID = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT TOP 1 bookingID FROM [HMS].[dbo].[Booking] ORDER BY bookingID DESC";

                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    bookingID = Convert.ToInt32(dr["bookingID"]);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return bookingID;
        }

        // Retrieve GuestID by BookingID
        public static int RetrieveGuestIDByBID(int bookingID)
        {
            int guestID = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT g.guestID AS GuestID" +
                                    " FROM Guest g, Booking b" +
                                    " WHERE b.bookingID = @bookingID AND b.guestID = g.guestID";

                comm.Parameters.AddWithValue("@bookingID", bookingID);

                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    guestID = Convert.ToInt32(dr["GuestID"]);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return guestID;
        }

        // Retrieve RoomID by BookingID
        public static int RetrieveRoomIDByBID(int bookingID)
        {
            int roomID = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT TOP 1 r.roomID AS Room_ID" +
                                    " FROM Room r, Booking b" +
                                    " WHERE r.bookingID = @bookingID";

                comm.Parameters.AddWithValue("@bookingID", bookingID);

                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    roomID = Convert.ToInt32(dr["Room_ID"]);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return roomID;
        }

        // Retrieve Number of Rows in ROOM
        public static int CheckRoomRows(int id)
        {
            int rows = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT COUNT(*) AS NoOfRoomsInBooking FROM [HMS].[dbo].[Room] WHERE bookingID=@id";
                comm.Parameters.AddWithValue("@id", id);

                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    rows = Convert.ToInt32(dr["NoOfRoomsInBooking"]);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rows;
        }

        // Retrieve - BOOKING (ALL)
        public static DataSet RetrieveAllBooking()
        {
            SqlConnection conn = null;
            SqlDataAdapter myCommand = null;
            DataSet ds = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                myCommand = new SqlDataAdapter("SELECT Booking.bookingID AS Booking_ID, Guest.name AS Guest_Name, Room.roomID AS Room_ID, Room.status As Room_Status, Booking.check_in_date AS Check_In_Date, Booking.check_out_date AS Check_Out_Date, Guest.paymentType As Payment" +
                                    " FROM [HMS].[dbo].[Booking]" +
                                    " INNER JOIN [HMS].[dbo].[Room]" +
                                        " ON Booking.bookingID=Room.bookingID" +
                                    " INNER JOIN [HMS].[dbo].[Guest]" +
                                        " ON Booking.guestID=Guest.guestID" +
                                    " ORDER BY Booking.bookingID", conn);

                ds = new DataSet();

                myCommand.Fill(ds);

            }
            catch (SqlException e)
            {
                throw e;
            }
            return ds;
        }

        // Retrieve - BOOKING (ID)
        public static DataSet RetrieveBookingByID(int bookingID)
        {
            SqlConnection conn = null;
            SqlDataAdapter da = null;
            SqlCommand comm = null;
            string selectSQL = null;
            DataSet ds = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                selectSQL = "SELECT Booking.bookingID AS Booking_ID, Guest.name AS Guest_Name, Room.roomID AS Room_ID, Room.status As Room_Status, Booking.check_in_date AS Check_In_Date, Booking.check_out_date AS Check_Out_Date, Guest.paymentType As Payment" +
                                    " FROM [HMS].[dbo].[Booking]" +
                                    " INNER JOIN [HMS].[dbo].[Room]" +
                                        " ON Booking.bookingID=Room.bookingID" +
                                    " INNER JOIN [HMS].[dbo].[Guest]" +
                                        " ON Booking.guestID=Guest.guestID" +
                                    " WHERE Booking.bookingID=@bookingID";

                comm = new SqlCommand(selectSQL, conn);
                da = new SqlDataAdapter();
                da.SelectCommand = comm;

                comm.Parameters.AddWithValue("@bookingID", bookingID);

                ds = new DataSet();

                da.Fill(ds);

            }
            catch (SqlException e)
            {
                throw e;
            }
            return ds;
        }

        // Retrieve - VIEW BOOKING (BID)
        public static Booking ViewBookingByBID(int bookingID)
        {
            Booking b = new Booking();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM [HMS].[dbo].[Booking] WHERE bookingID=@bookingID";
                comm.Parameters.AddWithValue("@bookingID", bookingID);

                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    b.Check_In_Date = (DateTime)dr["check_in_date"];
                    b.Check_Out_Date = (DateTime)dr["check_out_date"];
                    b.No_Of_Rooms = (int)dr["no_of_rooms"];
                    b.No_Of_Adults = (int)dr["no_of_adults"];
                    b.No_Of_Children = (int)dr["no_of_children"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return b;
        }

        // Retrieve - VIEW ROOM (RID)
        public static Room ViewRoomByRID(int roomID)
        {
            Room r = new Room();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM [HMS].[dbo].[Room] WHERE roomID=@roomID";
                comm.Parameters.AddWithValue("@roomID", roomID);

                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    r.RoomNo = (string)dr["roomNo"];
                    r.Room_Type = (string)dr["room_type"];
                    r.Bed_Type = (string)dr["bed_type"];
                    r.Status = (string)dr["status"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return r;
        }

        // Retrieve - VIEW GUEST (BID)
        public static Guest ViewGuestByBID(int bookingID)
        {
            Guest g = new Guest();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT g.name, g.phone, g.email, g.address, g.postalCode, g.country, g.paymentType" +
                                    " FROM Guest g, Booking b" +
                                    " WHERE b.bookingID=@bookingID AND b.guestID = g.guestID";
                comm.Parameters.AddWithValue("@bookingID", bookingID);

                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    g.Name = (string)dr["name"];
                    g.Phone = (int)dr["phone"];
                    g.Email = (string)dr["email"];
                    g.Address = (string)dr["address"];
                    g.PostalCode = (int)dr["postalCode"];
                    g.Country = (string)dr["country"];
                    g.PaymentType = (string)dr["paymentType"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return g;
        }

        // Retrieve - VIEW PAYMENT (BID)
        public static Payment ViewPaymentByBID(int bookingID)
        {
            Payment p = new Payment();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT p.cardholderName, p.creditCardNo, p.expiryDate" +
                                    " FROM Payment p, Booking b" +
                                    " WHERE b.bookingID=@bookingID AND p.guestID = b.guestID";
                comm.Parameters.AddWithValue("@bookingID", bookingID);

                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    p.CardholderName = (string)dr["cardholderName"];
                    p.CreditCardNo = (string)dr["creditCardNo"];
                    p.ExpiryDate = (DateTime)dr["expiryDate"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return p;
        }

        /* 
         * ---------- UPDATE -----------
         */
        // Update - CHECKIN
        public static int CheckIn(int bookingID)
        {
            int rowupdated = 0;
            string status = "Occupied";
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE [HMS].[dbo].[Room] SET status=@status WHERE bookingID=@bookingID";
                comm.Parameters.AddWithValue("@status", status);
                comm.Parameters.AddWithValue("@bookingID", bookingID);

                rowupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowupdated;
        }

        // Update - BOOKING
        public static int UpdateBooking(Booking b)
        {
            int rowadded = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE [HMS].[dbo].[Booking] SET Check_In_Date=@check_in_date, Check_Out_Date=@check_out_date, No_Of_Adults=@no_of_adults, No_Of_Children=@no_of_children" +
                    " WHERE BookingID=@bookingID";

                comm.Parameters.AddWithValue("@bookingID", b.BookingID);
                comm.Parameters.AddWithValue("@check_in_date", b.Check_In_Date);
                comm.Parameters.AddWithValue("@check_out_date", b.Check_Out_Date);
                comm.Parameters.AddWithValue("@no_of_adults", b.No_Of_Adults);
                comm.Parameters.AddWithValue("@no_of_children", b.No_Of_Children);

                rowadded = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowadded;
        }

        // Update - ROOM
        public static int UpdateRoom(Room r)
        {
            int rowadded = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE [HMS].[dbo].[Room] SET Room_Type=@room_type, Bed_Type=@bed_type, Status=@status" +
                    " WHERE RoomID=@roomID";

                comm.Parameters.AddWithValue("@roomID", r.RoomID);
                comm.Parameters.AddWithValue("@room_type", r.Room_Type);
                comm.Parameters.AddWithValue("@bed_type", r.Bed_Type);
                comm.Parameters.AddWithValue("@status", r.Status);

                rowadded = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowadded;
        }

        // Update - GUEST
        public static int UpdateGuest(Guest g)
        {
            int rowadded = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE [HMS].[dbo].[Guest] SET Name=@name, Phone=@phone, Email=@email, Address=@address, PostalCode=@postalCode, Country=@country, PaymentType=@paymentType" +
                    " WHERE GuestID=@guestID";

                comm.Parameters.AddWithValue("@name", g.Name);
                comm.Parameters.AddWithValue("@guestID", g.GuestID);
                comm.Parameters.AddWithValue("@phone", g.Phone);
                comm.Parameters.AddWithValue("@email", g.Email);
                comm.Parameters.AddWithValue("@address", g.Address);
                comm.Parameters.AddWithValue("@postalCode", g.PostalCode);
                comm.Parameters.AddWithValue("@country", g.Country);
                comm.Parameters.AddWithValue("@paymentType", g.PaymentType);

                rowadded = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowadded;
        }

        // Update - PAYMENT
        public static int UpdatePayment(Payment p)
        {
            int rowadded = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE [HMS].[dbo].[Payment] SET CardholderName=@cardholderName, ExpiryDate=@expiryDate, CreditCardNo=@creditCardNo" +
                    " WHERE GuestID=@guestID";

                comm.Parameters.AddWithValue("@guestID", p.GuestID);
                comm.Parameters.AddWithValue("@cardholderName", p.CardholderName);
                comm.Parameters.AddWithValue("@expiryDate", p.ExpiryDate);
                comm.Parameters.AddWithValue("@creditCardNo", p.CreditCardNo);

                rowadded = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowadded;
        }

        // Update - ROOM'S ROOM TYPE
        public static int UpdateRoomType(Room r)
        {
            int rowadded = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE [HMS].[dbo].[Room] SET Room_Type=@room_type" +
                    " WHERE RoomID=@roomID";

                comm.Parameters.AddWithValue("@roomID", r.RoomID);
                comm.Parameters.AddWithValue("@room_type", r.Room_Type);

                rowadded = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowadded;
        }

        // Update - ROOM'S BED TYPE
        public static int UpdateBedType(Room r)
        {
            int rowadded = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE [HMS].[dbo].[Room] SET Bed_Type=@bed_type, Room_Rates=@room_rates" +
                    " WHERE RoomID=@roomID";

                comm.Parameters.AddWithValue("@roomID", r.RoomID);
                comm.Parameters.AddWithValue("@bed_type", r.Bed_Type);
                comm.Parameters.AddWithValue("@room_rates", r.Room_Rates);

                rowadded = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowadded;
        }

        /* 
         * ---------- DELETE -----------
         */
        // Delete GUEST
        public static int DeleteGuest(int guestID)
        {
            int rowsdeleted = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM [HMS].[dbo].[Guest] WHERE guestID=@guestID";

                comm.Parameters.AddWithValue("@guestID", guestID);

                rowsdeleted = comm.ExecuteNonQuery();
                conn.Close();
            }

            catch (SqlException e)
            {
                throw e;
            }

            return rowsdeleted;
        }

        // Delete BOOKING
        public static int DeleteBooking(int bookingID)
        {
            int rowsdeleted = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM [HMS].[dbo].[Booking] WHERE bookingID=@bookingID";

                comm.Parameters.AddWithValue("@bookingID", bookingID);

                rowsdeleted = comm.ExecuteNonQuery();
                conn.Close();
            }

            catch (SqlException e)
            {
                throw e;
            }

            return rowsdeleted;
        }

        // Delete ROOM
        public static int DeleteRoom(int bookingID)
        {
            int rowsdeleted = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM [HMS].[dbo].[Room] WHERE bookingID=@bookingID";

                comm.Parameters.AddWithValue("@bookingID", bookingID);

                rowsdeleted = comm.ExecuteNonQuery();
                conn.Close();
            }

            catch (SqlException e)
            {
                throw e;
            }

            return rowsdeleted;
        }

        // Delete PAYMENT
        public static int DeletePayment(int guestID)
        {
            int rowsdeleted = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM [HMS].[dbo].[Payment] WHERE guestID=@guestID";

                comm.Parameters.AddWithValue("@guestID", guestID);

                rowsdeleted = comm.ExecuteNonQuery();
                conn.Close();
            }

            catch (SqlException e)
            {
                throw e;
            }

            return rowsdeleted;
        }
        #endregion 

        #region HouseKeeping
        public static Object[] GetHouseKeepingById(int id)
        {
            Object[] result = new Object[8];
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT Housekeeping.housekeepingID, Staff.staffID, Staff.name, Staff.duty_type, Room.roomID, Housekeeping.date, Housekeeping.progress, Room.status FROM [HMS].[dbo].[Housekeeping], [HMS].[dbo].[Staff], [HMS].[dbo].[Room] WHERE Housekeeping.staffID = Staff.staffID AND Housekeeping.roomID=Room.roomID AND Housekeeping.housekeepingID=@housekeepingID";
                comm.Parameters.AddWithValue("@housekeepingID", id);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    result[0] = (int)dr["housekeepingID"];
                    result[1] = (int)dr["staffID"];
                    result[2] = (string)dr["name"];
                    result[3] = (string)dr["duty_type"];
                    result[4] = (int)dr["roomID"];
                    result[5] = (DateTime)dr["date"];
                    result[6] = (string)dr["progress"];
                    result[7] = (string)dr["status"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return result;
        }

        public static int InsertHouseKeeping(HouseKeeping s)
        {
            int rowsInserted = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO [HMS].[dbo].[Housekeeping](date,staffID,roomID,progress)" +
                    " VALUES (@date,@staffID,@roomID,@progress)";
                comm.Parameters.AddWithValue("@date", s.Date);
                comm.Parameters.AddWithValue("@staffID", s.StaffID);
                comm.Parameters.AddWithValue("@roomID", s.RoomID);
                comm.Parameters.AddWithValue("@progress", s.Progress);
                rowsInserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsInserted;
        }

        public static bool compareHouseKeepingID(int id)
        {
            bool sucess = false;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT housekeepingID FROM [HMS].[dbo].[Housekeeping] where housekeepingID = @housekeepingID";
                comm.Parameters.AddWithValue("@housekeepingID", id);

                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    sucess = true;
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return sucess;
        }

        public static int UpdateHouseKeeping(HouseKeeping s)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE [HMS].[dbo].[Housekeeping] SET staffID=@staffID,roomID=@roomID,date=@date,progress=@progress where housekeepingID=@housekeepingID";
                comm.Parameters.AddWithValue("@housekeepingID", s.HouseKeepingID);
                comm.Parameters.AddWithValue("@staffID", s.StaffID);
                comm.Parameters.AddWithValue("@roomID", s.RoomID);
                comm.Parameters.AddWithValue("@date", s.Date);
                comm.Parameters.AddWithValue("@progress", s.Progress);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }

        public static int DeleteHouseKeeping(int id)
        {
            int rowsdeleted = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Delete From Housekeeping Where housekeepingID = @housekeepingID";

                comm.Parameters.AddWithValue("housekeepingID", id);
                rowsdeleted = comm.ExecuteNonQuery();
                conn.Close();
            }

            catch (SqlException e)
            {
                throw e;
            }

            return rowsdeleted;
        }

        public static bool compareStaffID(int id)
        {
            bool sucess = false;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT staffID FROM [HMS].[dbo].[Staff] where staffID = @staffID";
                comm.Parameters.AddWithValue("@staffID", id);

                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    sucess = true;
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return sucess;
        }

        public static bool compareRoomID(int id)
        {
            bool sucess = false;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT roomID FROM [HMS].[dbo].[Room] where roomID = @roomID";
                comm.Parameters.AddWithValue("@roomID", id);

                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    sucess = true;
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return sucess;
        }

        public static bool compareRoomStatus(int id)
        {
            bool sucess = false;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT roomID FROM [HMS].[dbo].[Room] where roomID = @roomID AND status = 'Vacant & Cleaning'";
                comm.Parameters.AddWithValue("@roomID", id);

                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    sucess = true;
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return sucess;
        }

        public static bool compareStaffDuty(int id)
        {
            bool sucess = false;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT staffID FROM [HMS].[dbo].[Staff] where staffID = @staffID AND duty_type = 'Room'";
                comm.Parameters.AddWithValue("@staffID", id);

                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    sucess = true;
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return sucess;
        }

        public static int UpdateHouseKeepingProgress(HouseKeeping s)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE [HMS].[dbo].[Housekeeping] SET progress=@progress where housekeepingID=@housekeepingID";
                comm.Parameters.AddWithValue("@housekeepingID", s.HouseKeepingID);
                comm.Parameters.AddWithValue("@progress", s.Progress);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }

        public static int UpdateRoomStatus(Room r)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE [HMS].[dbo].[Room] SET status=@status where roomID=@roomID";
                comm.Parameters.AddWithValue("@roomID", r.RoomID);
                comm.Parameters.AddWithValue("@status", r.Status);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }

        public static bool compareSchedule(int roomID, string progress)
        {
            bool sucess = false;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT roomID, progress FROM [HMS].[dbo].[Housekeeping] where roomID=@roomID AND progress=@progress";
                comm.Parameters.AddWithValue("@roomID", roomID);
                comm.Parameters.AddWithValue("@progress", progress);

                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    sucess = true;
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return sucess;
        }
        #endregion

        #region User
        /*
        *************************************
        * HMS USER ACCOUNT' C.R.U.D.
        *************************************
        */

        //Getting all User Accounts
        public static ArrayList GetAllUserAccounts()
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM [HMS].[dbo].[UserAccount]";

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    UserAccount user = new UserAccount();
                    user.Username = (string)dr["username"];
                    user.UserType = (string)dr["usertype"];
                    user.Password = (string)dr["password"];
                    result.Add(user);
                }

                dr.Close();
                conn.Close();
            }

            catch (SqlException e)
            {
                throw e;
            }

            return result;
        }

        //Getting the User Accounts By User Type
        public static ArrayList GetAllUserAccountsByUserType(string usertype)
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM [HMS].[dbo].[UserAccount] WHERE usertype=@usertype";
                comm.Parameters.AddWithValue("@usertype", usertype);

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    UserAccount user = new UserAccount();
                    user.Username = (string)dr["username"];
                    user.UserType = (string)dr["usertype"];
                    user.Password = (string)dr["password"];
                    result.Add(user);
                }

                dr.Close();
                conn.Close();
            }

            catch (SqlException e)
            {
                throw e;
            }

            return result;
        }

        //Getting an UserAccount by Username
        public static UserAccount GetUserAccountByUsername(string username)
        {
            UserAccount user = null;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM [HMS].[dbo].[UserAccount] WHERE username=@username";
                comm.Parameters.AddWithValue("@username", username);

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    user = new UserAccount();
                    user.Username = (string)dr["username"];
                    user.UserType = (string)dr["usertype"];
                    user.Password = (string)dr["password"];
                }

                dr.Close();
                conn.Close();
            }

            catch (SqlException e)
            {
                throw e;
            }

            return user;
        }

        //Creating an UserAccount
        public static int CreateUserAccount(UserAccount user)
        {
            int rowsInserted = 0;

            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO [HMS].[dbo].[UserAccount](username,usertype,password) VALUES (@username,@usertype,@password)";
                comm.Parameters.AddWithValue("@username", user.Username);
                comm.Parameters.AddWithValue("@usertype", user.UserType);
                comm.Parameters.AddWithValue("@password", user.Password);

                rowsInserted = comm.ExecuteNonQuery();
                conn.Close();
            }

            catch (SqlException e)
            {
                throw e;
            }

            return rowsInserted;
        }

        //Updating an UserAccount
        public static int UpdateUserAccount(UserAccount user)
        {
            int rowsUpdated = 0;

            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE [HMS].[dbo].[UserAccount] SET usertype=@usertype,password=@password WHERE username=@username";
                comm.Parameters.AddWithValue("@username", user.Username);
                comm.Parameters.AddWithValue("@usertype", user.UserType);
                comm.Parameters.AddWithValue("@password", user.Password);

                rowsUpdated = comm.ExecuteNonQuery();
                conn.Close();
            }

            catch (SqlException e)
            {
                throw e;
            }

            return rowsUpdated;
        }

        //Deleting an UserAccount
        public static int DeleteUserAccount(string username)
        {
            int rowsDeleted = 0;

            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM [HMS].[dbo].[UserAccount] WHERE username=@username";
                comm.Parameters.AddWithValue("@username", username);

                rowsDeleted = comm.ExecuteNonQuery();
                conn.Close();
            }

            catch (SqlException e)
            {
                throw e;
            }

            return rowsDeleted;
        }

        /*
        *************************************
        * HMS STAFF' C.R.U.D.
        *************************************
        */

        //Getting the Staffs
        public static ArrayList GetAllStaffs()
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM [HMS].[dbo].[Staff]";

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Staff s = new Staff();
                    s.StaffID = (int)dr["staffID"];
                    s.Name = (string)dr["name"];
                    s.DOB = (DateTime)dr["dob"];
                    s.BankNo = (int)dr["bank_no"];
                    s.Address = (string)dr["address"];
                    s.TelNo = (int)dr["tel_no"];
                    s.DutyType = (string)dr["duty_type"];
                    s.Username = (string)dr["username"];
                    result.Add(s);
                }

                dr.Close();
                conn.Close();
            }

            catch (SqlException e)
            {
                throw e;
            }

            return result;
        }

        //Getting a Staff by Username
        public static Staff GetStaffByUsername(string username)
        {
            Staff s = null;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM [HMS].[dbo].[Staff] WHERE username=@username";
                comm.Parameters.AddWithValue("@username", username);

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    s = new Staff();
                    s.StaffID = (int)dr["staffID"];
                    s.Name = (string)dr["name"];
                    s.DOB = (DateTime)dr["dob"];
                    s.BankNo = (int)dr["bank_no"];
                    s.Address = (string)dr["address"];
                    s.TelNo = (int)dr["tel_no"];
                    s.DutyType = (string)dr["duty_type"];
                    s.Username = (string)dr["username"];
                }

                dr.Close();
                conn.Close();
            }

            catch (SqlException e)
            {
                throw e;
            }

            return s;
        }

        //Creating a Staff
        public static int CreateStaff(Staff s)
        {
            int rowsInserted = 0;

            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO [HMS].[dbo].[Staff](name,dob,bank_no,address,tel_no,duty_type,username) VALUES (@name,@dob,@bank_no,@address,@tel_no,@duty_type,@username)";
                comm.Parameters.AddWithValue("@name", s.Name);
                comm.Parameters.AddWithValue("@dob", s.DOB);
                comm.Parameters.AddWithValue("@bank_no", s.BankNo);
                comm.Parameters.AddWithValue("@address", s.Address);
                comm.Parameters.AddWithValue("@tel_no", s.TelNo);
                comm.Parameters.AddWithValue("duty_type", s.DutyType);
                comm.Parameters.AddWithValue("@username", s.Username);

                rowsInserted = comm.ExecuteNonQuery();
                conn.Close();
            }

            catch (SqlException e)
            {
                throw e;
            }

            return rowsInserted;
        }

        //Updating a Staff
        public static int UpdateStaff(Staff s)
        {
            int rowsUpdated = 0;

            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE [HMS].[dbo].[Staff] SET name=@name,dob=@dob,bank_no=@bank_no,address=@address,tel_no=@tel_no,duty_type=@duty_type WHERE username=@username";
                comm.Parameters.AddWithValue("@name", s.Name);
                comm.Parameters.AddWithValue("@dob", s.DOB);
                comm.Parameters.AddWithValue("@bank_no", s.BankNo);
                comm.Parameters.AddWithValue("@address", s.Address);
                comm.Parameters.AddWithValue("@tel_no", s.TelNo);
                comm.Parameters.AddWithValue("@duty_type", s.DutyType);
                comm.Parameters.AddWithValue("@username", s.Username);

                rowsUpdated = comm.ExecuteNonQuery();
                conn.Close();
            }

            catch (SqlException e)
            {
                throw e;
            }

            return rowsUpdated;
        }

        //Deleting a Staff
        public static int DeleteStaff(string username)
        {
            int rowsDeleted = 0;

            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM [HMS].[dbo].[Staff] WHERE username=@username";
                comm.Parameters.AddWithValue("@username", username);

                rowsDeleted = comm.ExecuteNonQuery();
                conn.Close();
            }

            catch (SqlException e)
            {
                throw e;
            }

            return rowsDeleted;
        }
        #endregion

        #region General Report
        //Get Table for General Report
        public static ArrayList getGeneralReport(string roomNo)
        {
            ArrayList overviewList = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                //Get necessary details for table
                comm.CommandText = "SELECT g.name,roomNo,b.check_in_date FROM Guest g,Room r, Booking b WHERE roomNo=@roomNo and r.bookingID=b.bookingID and b.guestID=g.guestID ORDER BY check_in_date desc";
                comm.Parameters.AddWithValue("@roomNo", roomNo);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    General o = new General();
                    o.Guests = (String)dr["name"];
                    o.RoomNo = (String)dr["roomNo"];
                    DateTime check_in = (DateTime)dr["check_in_date"];
                    o.Check_in = Convert.ToString(check_in.Year) + "," + Convert.ToString(check_in.Month) + "," + Convert.ToString(check_in.Day);
                    overviewList.Add(o);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return overviewList;
        }

        //Check current status based on a room number
        public static String getGeneralStatus(string roomNo)
        {
            String status = "";
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT status FROM Room WHERE roomNo=@roomNo";
                comm.Parameters.AddWithValue("@roomNo", roomNo);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    status = (String)dr["status"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return status;
        }
        #endregion

        #region Overview Report
        //Get Table Details for Overview Report
        public static List<Overview> getOverviewReport(String date)
        {
            List<Overview> generalList = new List<Overview>();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT g.name, r.roomNo ,b.check_in_date,b.check_out_date FROM Guest g,Room r,Booking b WHERE check_out_date>=@date and check_in_date<=@date and r.bookingID=b.bookingID and b.guestID=g.guestID ORDER BY roomNo asc";
                comm.Parameters.AddWithValue("@date", Convert.ToDateTime(date));
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Overview o = new Overview();
                    o.Guests = (String)dr["name"];
                    o.RoomNo = (string)dr["roomNo"];
                    DateTime check_in = (DateTime)dr["check_in_date"];
                    o.Check_in = Convert.ToString(check_in.Year) + "," + Convert.ToString(check_in.Month) + "," + Convert.ToString(check_in.Day);
                    DateTime check_out = (DateTime)dr["check_out_date"];
                    o.Check_out = Convert.ToString(check_out.Year) + "," + Convert.ToString(check_out.Month) + "," + Convert.ToString(check_out.Day);
                    generalList.Add(o);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return generalList;
        }
        #endregion

        #region Room Status Report
        //Get Table Details
        public static ArrayList getAllRoomByStatus(string status)
        {
            ArrayList roomList = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT DISTINCT roomNo,room_type,status FROM Room WHERE status=@status";
                comm.Parameters.AddWithValue("@status", status);
                //comm.Parameters.AddWithValue("@status", roomstatus);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Room r = new Room();
                    r.RoomNo = (string)dr["roomNo"];
                    r.Room_Type = (String)dr["room_type"];
                    r.Status = (String)dr["status"];
                    roomList.Add(r);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return roomList;
        }

        //Get Pie Chart Details
        public static ArrayList getRoomStatusCount()
        {
            ArrayList roomstatusCountList = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT [status],Count(DISTINCT roomNo) as \"count\" from Room Group By [status]";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    String[] roomstatusCount = new String[2];
                    roomstatusCount[0] = (String)dr["status"];
                    roomstatusCount[1] = Convert.ToString((int)dr["count"]);
                    roomstatusCountList.Add(roomstatusCount);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return roomstatusCountList;
        }
        #endregion

        #region Housekeeping Report
        //Get Table Details
        public static List<HKReport> getHousekeepingReport(DateTime startdate, DateTime enddate)
        {
            List<HKReport> housekeepingList = new List<HKReport>();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT housekeepingID, name, duty_type, date FROM Housekeeping h, Staff s Where h.staffID=s.staffID and date>=@startdate and date<=@enddate ORDER BY date desc";
                comm.Parameters.AddWithValue("@startdate", startdate);
                comm.Parameters.AddWithValue("@enddate", enddate);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    HKReport hk = new HKReport();
                    hk.HousekeepingID = (int)dr["housekeepingID"];
                    hk.Name = (String)dr["name"];
                    hk.Duty_type = (String)dr["duty_type"];
                    DateTime date = (DateTime)dr["date"];
                    hk.Date = Convert.ToString(date.Year) + "," + Convert.ToString(date.Month) + "," + Convert.ToString(date.Day);//((DateTime)dr["date"]).ToString("dd/MM/yyyy");
                    housekeepingList.Add(hk);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return housekeepingList;
        }

        //Get Column Chart Details by day
        public static ArrayList getHousekeepingByDay(DateTime startdate, DateTime enddate)
        {
            ArrayList HousekeepingChartList = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                //Count the number of records in a date
                comm.CommandText = "SELECT date, COUNT(*) as \"Count\" FROM Housekeeping WHERE date>=@startdate and date<=@enddate GROUP BY date";
                comm.Parameters.AddWithValue("@startdate", startdate);
                comm.Parameters.AddWithValue("@enddate", enddate);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Object[] row = new Object[2];
                    row[0] = ((DateTime)dr["date"]).ToLongDateString();
                    row[1] = (int)dr["Count"];
                    HousekeepingChartList.Add(row);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return HousekeepingChartList;
        }

        //Get Column Chart Details by week
        public static ArrayList getHousekeepingByWeek(DateTime startdate, DateTime enddate)
        {
            ArrayList HousekeepingChartList = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                //Count the number of records in a week using week number
                comm.CommandText = "SELECT DATEPART(WEEK,date) as \"Week\", Year(date) as \"Year\", Count(*) as \"Count\" FROM Housekeeping WHERE date>=@startdate and date<=@enddate GROUP BY DATEPART(WEEK,date), YEAR(date)";
                comm.Parameters.AddWithValue("@startdate", startdate);
                comm.Parameters.AddWithValue("@enddate", enddate);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    int[] row = new int[3];
                    row[0] = (int)dr["Week"];
                    row[1] = (int)dr["Count"];
                    row[2] = (int)dr["Year"];
                    HousekeepingChartList.Add(row);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return HousekeepingChartList;
        }

        //Get Column Chart Details by month
        public static ArrayList getHousekeepingByMonth(DateTime startdate, DateTime enddate)
        {
            ArrayList HousekeepingChartList = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                //Count the number of records in a month using month number
                comm.CommandText = "SELECT DATEPART(MONTH,date) as \"Period\", YEAR(date) as \"Year\", Count(*) as \"Count\" FROM Housekeeping WHERE date>=@startdate and date<=@enddate GROUP BY DATEPART(MONTH,date), YEAR(date)";
                comm.Parameters.AddWithValue("@startdate", startdate);
                comm.Parameters.AddWithValue("@enddate", enddate);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    int[] row = new int[3];
                    row[0] = (int)dr["Period"];
                    row[1] = (int)dr["Count"];
                    row[2] = (int)dr["Year"];
                    HousekeepingChartList.Add(row);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return HousekeepingChartList;
        }
        #endregion

        #region Room Occupancy Report
        //Get Table Details
        public static List<RoomOccupancy> getRoomOccupancyReport(string startdate, string enddate)
        {
            List<RoomOccupancy> roomoccupancyList = new List<RoomOccupancy>();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT roomNo, check_in_date, DATEDIFF(day,check_in_date,check_out_date) as \"days\",check_out_date FROM Booking b, Room r WHERE b.bookingID=r.bookingID and check_in_date not in (SELECT check_in_date FROM Booking WHERE check_in_date>@enddate) and check_out_date not in (SELECT check_out_date FROM Booking WHERE check_out_date<@startdate)";
                comm.Parameters.AddWithValue("@startdate", Convert.ToDateTime(startdate));
                comm.Parameters.AddWithValue("@enddate", Convert.ToDateTime(enddate));
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    RoomOccupancy ro = new RoomOccupancy();
                    DateTime check_in = (DateTime)dr["check_in_date"];
                    ro.Check_in = Convert.ToString(check_in.Year) + "," + Convert.ToString(check_in.Month) + "," + Convert.ToString(check_in.Day);
                    ro.RoomNo = (string)dr["roomNo"];
                    ro.Daysoccupied = (int)dr["days"];
                    DateTime check_out = (DateTime)dr["check_out_date"];
                    ro.Check_out = Convert.ToString(check_out.Year) + "," + Convert.ToString(check_out.Month) + "," + Convert.ToString(check_out.Day);//((DateTime)dr["date"]).ToString("dd/MM/yyyy");
                    roomoccupancyList.Add(ro);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return roomoccupancyList;
        }

        //Get Column Chart Details by day
        public static Object[] getRoomOccupancyByDay(DateTime date)
        {
            Object[] ro = new Object[2];
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT Count(DISTINCT roomNo)*100.0/100 as \"Count\" FROM Room r, Booking b WHERE r.bookingID=b.bookingID and check_in_date<=@date and check_out_date>=@date";
                comm.Parameters.AddWithValue("@date", date);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    ro[0] = date.ToLongDateString();
                    ro[1] = Math.Round(Convert.ToDouble((decimal)dr["Count"]), 2);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return ro;
        }

        //Get Column Chart Details by week
        public static Object[] getRoomOccupancyByWeek(int week, int year)
        {
            Object[] ro = new Object[2];
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT COUNT(DISTINCT roomNo)*100.0/100 as \"Count\" FROM Booking b, Room r WHERE b.bookingID=r.bookingID and b.bookingID in (SELECT bookingID FROM Booking WHERE DATEPART(WEEK,check_in_date)=@week or DATEPART(WEEK,check_out_date)=@week) and YEAR(check_in_date)=@year and YEAR(check_out_date)=@year";
                comm.Parameters.AddWithValue("@week", week);
                comm.Parameters.AddWithValue("@year", year);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    ro[0] = week;
                    if ((Object)dr["Count"] is System.DBNull)
                    {
                        ro[1] = 0.0;
                    }
                    else
                    {
                        ro[1] = Math.Round(Convert.ToDouble((decimal)dr["Count"]), 2);
                    }
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return ro;
        }

        //Get Column Chart Details by month
        public static Object[] getRoomOccupancyByMonth(DateTime date)
        {
            Object[] ro = new Object[3];
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT COUNT(DISTINCT roomNo)*100.0/100 as \"Count\" FROM Booking b, Room r WHERE b.bookingID=r.bookingID and b.bookingID in (SELECT bookingID FROM Booking WHERE DATEPART(MONTH,check_in_date)=@month or DATEPART(MONTH,check_out_date)=@month) and YEAR(check_in_date)=@year and YEAR(check_out_date)=@year";
                comm.Parameters.AddWithValue("@month", date.Month);
                comm.Parameters.AddWithValue("@year", date.Year);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    ro[0] = date.Month;
                    if ((Object)dr["Count"] is System.DBNull)
                    {
                        ro[1] = 0.0;
                    }
                    else
                    {
                        ro[1] = Math.Round(Convert.ToDouble((decimal)dr["Count"]), 2);
                    }
                    ro[2] = date.Year;
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return ro;
        }
        #endregion

        #region Checkout
        //get Invoice
        public static Object[] getInvoiceByBookingID(int bookingID)
        {
            Object[] invoice = new Object[4];
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT b.bookingID, g.guestID, name, phone, email, [address], postalCode, country, check_in_date,check_out_date, no_of_rooms, no_of_adults, no_of_children, room_type, bed_type, paymentType, room_rates FROM Booking b, Guest g, Room r WHERE g.guestID=b.guestID and r.bookingID=b.bookingID and b.bookingID=@bookingID";
                comm.Parameters.AddWithValue("@bookingID", bookingID);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    Booking b = new Booking();
                    b.BookingID = (int)dr["bookingID"];
                    b.Check_In_Date = (DateTime)dr["check_in_date"];
                    b.Check_Out_Date = (DateTime)dr["check_out_date"];
                    b.No_Of_Rooms = (int)dr["no_of_rooms"];
                    b.No_Of_Adults = (int)dr["no_of_adults"];
                    b.No_Of_Children = (int)dr["no_of_children"];
                    Guest g = new Guest();
                    g.GuestID = (int)dr["guestID"];
                    g.Name = (string)dr["name"];
                    g.Phone = (int)dr["phone"];
                    g.PaymentType = (string)dr["paymentType"];
                    Payment p = null;
                    if (string.Compare(g.PaymentType, "Cash", true) == 0)
                    {
                        p = new Payment();
                        p = DatabaseManager.getPaymentDetails(g.GuestID);
                    }
                    Object email = (Object)dr["email"];
                    if (!(email is System.DBNull))
                    {
                        g.Email = (string)dr["email"];
                    }
                    else
                    {
                        g.Email = "-";
                    }
                    g.Address = (string)dr["address"];
                    Object country = (Object)dr["country"];
                    if (!(country is System.DBNull))
                    {
                        g.Country = (string)dr["country"];
                    }
                    else
                    {
                        g.Country = "-";
                    }
                    g.PostalCode = (int)dr["postalCode"];
                    Room r = new Room();
                    r.Room_Type = (string)dr["room_type"];
                    r.Room_Rates = Convert.ToDouble((decimal)dr["room_rates"]);
                    r.Bed_Type = (string)dr["bed_type"];
                    invoice[0] = b;
                    invoice[1] = g;
                    invoice[2] = r;
                    invoice[3] = p;
                }
            }
            catch (SqlException e)
            {
                throw e;
            }
            return invoice;
        }

        //get Payment Details if have
        public static Payment getPaymentDetails(int guestID)
        {
            Payment p = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Payment WHERE guestID=@guestID";
                comm.Parameters.AddWithValue("@guestID", guestID);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    p = new Payment();
                    p.ExpiryDate = (DateTime)dr["expiryDate"];
                    p.CreditCardNo = (string)dr["creditCardNo"];
                }
            }
            catch (SqlException e)
            {
                throw e;
            }
            return p;
        }

        //get list of roomIDs based on a bookingID
        public static ArrayList getRoomIDByBookingID(int bookingID)
        {
            ArrayList roomIDList = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT roomID FROM Booking b, Room r WHERE b.bookingID=r.bookingID and b.bookingID=@bookingID";
                comm.Parameters.AddWithValue("@bookingID", bookingID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    roomIDList.Add((int)dr["roomID"]);
                }
            }
            catch (SqlException e)
            {
                throw e;
            }
            return roomIDList;
        }
        #endregion
    }
}